package com.xbts.screen.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.xbts.screen.domain.vo.WeProduceInformationIndexStatisticsVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @author : llk
 * @Date: 2023/12/29 15:35
 */
public interface ScreenMapper {

    /**
     * 大屏业务信息目标
     * @return
     */
    @Select("SELECT DATA0015.ppc_bal_value 月接单目标面积  FROM DATA0015 WHERE RKEY=22")
    Double monthlyOrderTargetArea();

    /**
     * 按单量查询
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return
     */
//    @Select("SELECT ROUND(SUM(DATA0060.PARTS_ORDERED*DATA0050.unit_sq), 2) orderArea, CONVERT(varchar(100),data0060.ENT_DATE ,23) orderTime FROM DATA0060 INNER JOIN DATA0050 ON DATA0050.RKEY=DATA0060.CUST_PART_PTR " +
//            "WHERE DATA0060.ENT_DATE >= #{startTime}  AND DATA0060.ENT_DATE <= #{endTime} " +
//            "GROUP BY CONVERT(varchar(100),data0060.ENT_DATE ,23)")
    List<Map> getOrderByTime(@Param("startTime") String startTime, @Param("endTime") String endTime,
                             @Param("factoryName")String factoryName);

    List<Map> getOrderByMonth(@Param("startTime") String startTime, @Param("endTime") String endTime,
                              @Param("factoryName")String factoryName);

    Map qualityInformation(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("SELECT SUM(DATA0060.PARTS_ORDERED*DATA0050.unit_sq) orderArea,DATA0015.ABBR_NAME factoryName,avg(DATA0015.ppc_bal_value) target\n" +
            "FROM DATA0060 INNER JOIN DATA0050 ON DATA0050.RKEY=DATA0060.CUST_PART_PTR\n" +
            "              INNER JOIN DATA0015 ON DATA0015.RKEY=DATA0060.WHSE_PTR\n" +
            "WHERE DATA0060.ENT_DATE>= #{startTime}  AND DATA0060.ENT_DATE<= #{endTime}\n" +
            "GROUP BY DATA0015.ABBR_NAME,DATA0015.ppc_bal_value\n" +
            "union select 123.35 orderArea,'二厂' factoryName, 3000 target\n" +
            "union select 333.442 orderArea,'三厂' factoryName, 4000 target")
    List<Map> businessInformationDetail(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @DS("wecan")
    @Select("SELECT TOP\n" +
        "100 PERCENT SUM ( ROUND( Data0053.QUANTITY * ISNULL( Data0053.unit_area, DATA0050.UNIT_SQ ), 4 ) ) AS storage_area,\n" +
        "CONVERT ( VARCHAR ( 100 ), data0416.sys_date , 23 ) storage_time \n" +
        "FROM\n" +
        "Data0053 ( nolock )\n" +
        "INNER JOIN Data0050 ( nolock ) ON Data0053.CUST_PART_PTR= Data0050.RKEY\n" +
        "INNER JOIN Data0025 ( nolock ) ON Data0050.RKEY= Data0025.ANCESTOR_PTR \n" +
        "AND Data0025.PARENT_PTR= 0\n" +
        "LEFT JOIN Data0015 ( nolock ) ON Data0053.WHSE_PTR = Data0015.RKEY\n" +
        "LEFT JOIN Data0016 ( nolock ) ON Data0053.LOC_PTR = Data0016.RKEY\n" +
        "LEFT JOIN Data0008 ( nolock ) ON Data0025.PROD_CODE_PTR= Data0008.RKEY\n" +
        "LEFT JOIN data0007 ( nolock ) ON data0008.pr_grp_pointer= data0007.rkey\n" +
        "LEFT JOIN Data0010 ( nolock ) ON Data0053.customer_ptr = Data0010.RKEY\n" +
        "LEFT JOIN data0009 ( nolock ) ON data0010.sales_rep_ptr= data0009.rkey\n" +
        "LEFT JOIN data0059 ( nolock ) ON Data0053.CUST_PART_PTR= data0059.cust_part_ptr  AND Data0053.customer_ptr= Data0059.customer_ptr\n" +
        "LEFT JOIN data0064 ( nolock ) ON data0064.rkey= data0053.barcode_ptr\n" +
        "LEFT JOIN data0060 e ( nolock ) ON e.rkey= data0064.so_ptr\n" +
        "LEFT JOIN data0416 ( nolock ) ON Data0053.NPAD_PTR= data0416.rkey\n" +
        "LEFT JOIN Data0006 ( nolock ) ON Data0053.WORK_ORDER_PTR = Data0006.RKEY\n" +
        "LEFT JOIN Data0060 ( nolock ) ON Data0006.SO_ptr= Data0060.rkey\n" +
        "LEFT JOIN Data0060 b ( nolock ) ON data0053.SO_ptr= b.rkey\n" +
        "LEFT JOIN Data0012 ( nolock ) ON Data0060.CUST_SHIP_ADDR_PTR = Data0012.RKEY\n" +
        "LEFT JOIN Data0189 ( nolock ) ON Data0012.SHIP_CTAX_PTR = Data0189.RKEY\n" +
        "LEFT JOIN data0097 ( nolock ) ON data0060.purchase_order_ptr= data0097.rkey\n" +
        "LEFT JOIN DATA0098 ( nolock ) ON data0416.rma_ptr= data0098.rkey\n" +
        "LEFT JOIN data0001 ( nolock ) ON data0060.currency_ptr= data0001.rkey\n" +
        "LEFT JOIN data0022 ( nolock ) ON data0053.srce_ptr= data0022.rkey\n" +
        "LEFT JOIN data0456 ( nolock ) ON data0022.grn_ptr= data0456.rkey\n" +
        "LEFT JOIN data0071 ( nolock ) ON data0022.SOURCE_PTR= data0071.RKEY\n" +
        "LEFT JOIN data0070 ( nolock ) ON data0071.PO_PTR= data0070.RKEY\n" +
        "LEFT JOIN data1439 ( nolock ) ON data0416.type= 9 \n" +
        "AND data0416.trans_ptr= data1439.rkey\n" +
        "LEFT JOIN data0005 ON data0053.EMPLOYEE_PTR= data0005.rkey\n" +
        "LEFT JOIN data0023 ( nolock ) ON data0070.supplier_pointer= data0023.rkey\n" +
        "LEFT JOIN data0071 AS e71 ON e71.SO_PTR= e.RKEY\n" +
        "LEFT JOIN data0047 a ( nolock ) ON data0025.rkey= a.source_pointer \n" +
        "AND a.parameter_ptr= 2 \n" +
        "WHERE\n" +
        "1 = 1 \n" +
        "AND isnull( data1439.BillDate, isnull( data0416.sys_date, data0053.MFG_DATE ) ) >=#{startTime} \n" +
        "AND isnull( data1439.BillDate, isnull( data0416.sys_date, data0053.MFG_DATE ) ) <=#{endTime} \n" +
        "AND isnull( type, 2 ) = 2 \n" +
        "AND ( Data0025.PARENT_PTR = 0 ) \n" +
        "AND Data0053.QUANTITY> 0 \n" +
        "AND isnull( Data0416.tstatus, 2 ) = 2 \n" +
        "AND ( isnull( Data0053.WHSE_PTR, 0 ) = 0 OR EXISTS ( SELECT 1 FROM get_factory_ptrTable ( 1 ) WHERE whse_rkey = Data0053.WHSE_PTR ) ) \n" +
        "AND ( Data0016.isProcess<> 1 ) \n" +
        "GROUP BY\n" +
        "CONVERT ( VARCHAR ( 100 ), data0416.sys_date , 23 )")
    List<WeProduceInformationIndexStatisticsVO> produceInformation(@Param("startTime") String startTime, @Param("endTime") String endTime);
}
